package com.huike.stock_analysis.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.huike.stock_analysis.entity.News;
import com.huike.stock_analysis.entity.Rank;



/**
 * 
 * @author mzLEI
 * @date 2020年7月2日
 */

@Repository
public class SearchPageDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	private int ranking = 0;
	
	/**
	 * 
	 * @param content
	 * @return
	 * 搜索的股票是否存在
	 */
	public boolean isExist(String stock) {
		String sql = "SELECT count(*) FROM `ads_zjtj1` where `ads_id` = " + stock +
				" or `ads_name` = " + stock;
		if(jdbcTemplate.queryForObject(sql, Integer.class) > 0){
			return true;
		}
		return false;
	}
	/**
	 *  
	 * @param stock
	 * @return
	 * 根据搜索内容查询股票代码
	 */
	public String queryCode(String stock) {
		String sql = "SELECT `ads_id` FROM `ads_zjtj1` where `ads_id` = " + stock +
				" or `ads_name` = " + stock + " LIMIT 1";
		
		return jdbcTemplate.queryForObject(sql, String.class);
	}
	
	/**
	 * 
	 * @return
	 * 最新日期
	 */
	public String latestDate() {
		String sql = "SELECT MAX(`ads_date`) FROM `ads_jszb`";
		return jdbcTemplate.queryForObject(sql, String.class);
	}
	/**
	 * 
	 * @param code
	 * @return 股票涨跌幅前十
	 */

	public List<Rank> stockRank(){
		String date = latestDate();
		String sql = "SELECT `ads_name`, `ads_id`, `ads_rzdf` FROM `ads_jszb` WHERE `ads_date` = '"
					+date +"' ORDER BY `ads_rzdf` DESC LIMIT 10";
		
		List<Rank> ranks = jdbcTemplate.query(sql, new RowMapper<Rank>() {

			@Override
			public Rank mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				Rank rank = new Rank();
				rank.setRanking(++ranking);
				
				rank.setChange(rs.getDouble("ads_rzdf"));
				rank.setName(rs.getString("ads_name"));
				rank.setCode(rs.getString("ads_id"));
				
				return rank;
			}
			
		});
		ranking = 0;
		return ranks;
	}
	/**
	 * 
	 * @return
	 * 新闻信息
	 */
	public List<News> news(){
		
		String sql = "SELECT * FROM `ads_news` ORDER BY `ads_date`, `ads_time` DESC LIMIT 30";
		
		List<News> newses = jdbcTemplate.query(sql, new RowMapper<News>() {

			@Override
			public News mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				News news = new News();
				news.setDate(rs.getDate("ads_date").toString());
				news.setTime(rs.getString("ads_time"));
				news.setTitle(rs.getString("ads_title"));
				news.setAuthor(rs.getString("ads_author"));
				news.setSource(rs.getString("ads_source"));
				news.setContent(rs.getString("ads_content"));
				return news;
			}
			
		});
		
		return newses;
	}
}
